RefreshLink Method Example

This example uses the RefreshLink method to refresh the data in a linked table after its connection has been changed from one data source to another. The RefreshLinkOutput procedure is required for this procedure to run.

Sub RefreshLinkX()

    Dim dbsCurrent As Database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be
    ' appended.
    Set dbsCurrent = OpenDatabase("DB1.mdb")

    ' Create a linked table that points to a Microsoft 
    ' SQL Server database.
    Set tdfLinked = _
        dbsCurrent.CreateTableDef("AuthorsTable")
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
    tdfLinked.SourceTableName = "authors"
    dbsCurrent.TableDefs.Append tdfLinked

    ' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to first source:"
    RefreshLinkOutput dbsCurrent

    ' Change connection information for linked table and 
    ' refresh the connection in order to make the new data 
    ' available.
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=NewPublishers"
    tdfLinked.RefreshLink

    ' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to second source:"
    RefreshLinkOutput dbsCurrent

    ' Delete linked table because this is a demonstration.
    dbsCurrent.TableDefs.Delete tdfLinked.Name

    dbsCurrent.Close

End Sub

Sub RefreshLinkOutput(dbsTemp As Database)

    Dim rstRemote As Recordset
    Dim intCount As Integer

    ' Open linked table.
    Set rstRemote = _
        dbsTemp.OpenRecordset("AuthorsTable")

    intCount = 0

    ' Enumerate Recordset object, but stop at 50 records.
    With rstRemote
        Do While Not .EOF And intCount < 50
            Debug.Print , .Fields(0), .Fields(1)
            intCount = intCount + 1
            .MoveNext
        Loop
        If Not .EOF Then Debug.Print , "[more records]"
        .Close
    End With

End Sub